use master
go
if exists (select name from sys.databases where name = 'AlluringDecors')
drop database [AlluringDecors]
go
create database AlluringDecors
go
use AlluringDecors
go
create table Users
(
	Username nvarchar(50) not null,
	[Password] nvarchar(50),
	Fullname nvarchar(50),
	Email nvarchar(50),
	Gender nvarchar(10),
	Birthdate datetime,
	[Address] nvarchar(50),
	Phone nvarchar(20),
	Date_Created datetime,
	[Status] bit default 1,
	[Role] nvarchar(20),
	constraint [PK_Users] primary key CLUSTERED (Username asc)
)
insert into Users values('admin', 'admin', 'Trinh Xuan Thuy', 'thuyxuan.mylove@gmail.com', 'Male', '6/18/1990', 'D2 Binh Thanh', '0985140951', '9/10/2014', 1, 'Role_Admin')
insert into Users values('user', 'user', 'Dinh Hong Quan', 'hongquan@gmail.com', 'FeMale', '6/18/1993', 'D2 Binh Thanh', '012345789', '9/10/2014', 1, 'Role_User')
select * from Users
go
create table Domain
(
	Domain_ID int identity(1,1) not null,
	Name nvarchar(255),
	[Status] bit default 1,
	Descriptions ntext,
	constraint [PK_Domain] primary key CLUSTERED (Domain_ID asc)
)
go
create table [Service]
(
	Service_ID int identity(1,1) not null,
	Name nvarchar(100),
	Descriptions ntext,
	DetailDescription ntext,
	Price money,
	[Status] bit default 1,
	Domain_ID int foreign key references Domain(Domain_ID),
	constraint [PK_Service] primary key CLUSTERED (Service_ID asc)
)
go
create table Photo
(
	Photo_ID int identity(1,1) not null,
	images nvarchar(255),
	constraint [PK_Photo] primary key CLUSTERED (Photo_ID asc)
)
go
create table SerPhoto
(
	Service_ID int not null foreign key references [Service](Service_ID),
	Photo_ID int not null foreign key references Photo(Photo_ID),
	constraint [PK_SerPhoto] primary key CLUSTERED (Service_ID, Photo_ID)
)
go
create table Orders
(
	Order_ID int identity(1,1) not null,
	Total money,
	Reason ntext,
	DatePaid datetime,
	DateCreated datetime,
	Username nvarchar(50) foreign key references Users(Username),
	constraint [PK_Orders] primary key CLUSTERED (Order_ID asc)
)
go
create table OrderDetail
(
	Detail_ID int identity(1,1) not null,
	Location nvarchar(50),
	Price money,
	[Status] nvarchar(50),
	Deleted bit default 1,
	Order_ID int foreign key references Orders(Order_ID),
	Service_ID int foreign key references [Service](Service_ID),
	constraint [PK_OrderDetail] primary key CLUSTERED (Detail_ID asc)
)
go
create table FAQs
(
	FAQ_ID int identity(1,1) not null,
	Question nvarchar(255),
	Answer ntext,
	[Status] bit default 1,
	constraint [PK_FAQs] primary key CLUSTERED (FAQ_ID asc)
)
go
create table Feedback
(
	Feedback_ID int identity(1,1) not null,
	Question nvarchar(255),
	Username nvarchar(50) foreign key references Users(Username),
	constraint [PK_Feedback] primary key CLUSTERED (Feedback_ID asc)
)
go

